package com.youboy.wechatapi.utils.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MyDBCon extends BaseDBCon {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private String dbtype = "";
	int len1 = 0;
	int len2 = 0;

	public MyDBCon(String db) {
		dbtype = db;
	}

	@SuppressWarnings("static-access")
	public int insertSQL(String sql, String[] types, String[] values) {
		try {
			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sql);
			setPS(ps, types, values);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return -1;
	}

	@SuppressWarnings("static-access")
	public String insertSQLUg(String sql, String[] types, String[] values) {
		try {
			String reStr = "";
			conn = this.getConnection(dbtype);

			ps = conn.prepareStatement(sql);
			setPS(ps, types, values);
			ps.executeUpdate();
			ps = conn.prepareStatement("SELECT last_insert_id()");
			rs = ps.executeQuery();
			if (rs.next())
				reStr = String.valueOf(rs.getInt(1));
			return reStr;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return "";
	}

	@SuppressWarnings("static-access")
	public int updateSQL(String sql, String[] types, String[] values) {
		try {
			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sql);
			setPS(ps, types, values);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			close();
		}
	}

	@SuppressWarnings("static-access")
	public int delSQL(String sql, String[] types, String[] values) {
		try {
			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sql);
			setPS(ps, types, values);
			int i = ps.executeUpdate();
			return i;
		} catch (SQLException e) {
			return 0;
		} finally {
			close();
		}
	}

	@SuppressWarnings({ "static-access", "rawtypes", "unchecked" })
	public List getList(String sCmd, String[] types, String[] values) {
		List vTmpHandle = new ArrayList();
		try {
			if (sCmd.length() == 0) {
				return vTmpHandle;
			}
			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sCmd);
			setPS(ps, types, values);
			rs = ps.executeQuery();
			if (rs == null) {
				return vTmpHandle;
			}
			int iColumn = rs.getMetaData().getColumnCount();
			if (rs.next()) {

				for (int i = 1; i <= iColumn; i++) {
					String sTmpHandle = rs.getString(i) + " ";
					if (sTmpHandle.trim().equalsIgnoreCase("null")) {
						vTmpHandle.add("");
					} else {
						vTmpHandle.add(sTmpHandle.trim());
					}
				}
				return vTmpHandle;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return vTmpHandle;
	}

	@SuppressWarnings({ "rawtypes", "static-access", "unchecked" })
	public List getListIsolActionRead(String sCmd, String[] types,
			String[] values) {
		List vTmpHandle = new ArrayList();
		try {
			if (sCmd.length() == 0) {
				return vTmpHandle;
			}
			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sCmd);
			setPS(ps, types, values);
			ps.executeQuery("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
			rs = ps.executeQuery();
			if (rs == null) {
				return vTmpHandle;
			}
			int iColumn = rs.getMetaData().getColumnCount();
			if (rs.next()) {
				for (int i = 1; i <= iColumn; i++) {
					String sTmpHandle = rs.getString(i) + " ";
					if (sTmpHandle.trim().equalsIgnoreCase("null")) {
						vTmpHandle.add("");
					} else {
						vTmpHandle.add(sTmpHandle.trim());
					}
				}
				return vTmpHandle;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return vTmpHandle;
	}

	@SuppressWarnings({ "rawtypes", "static-access", "unchecked" })
	public List getListResultSetIsolActionRead(String sCmd, String[] types,
			String[] values) {
		List<Object> vHandle = new ArrayList<Object>();
		try {
			if (sCmd.length() == 0) {
				return vHandle;
			}
			sCmd = sCmd.toLowerCase();

			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sCmd);
			setPS(ps, types, values);
			ps.executeQuery("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
			rs = ps.executeQuery();
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				List vTmpHandle = new ArrayList(iColumn);
				for (int i = 1; i <= iColumn; i++) {
					String sTmpHandle = rs.getString(i) + " ";
					if (sTmpHandle.trim().equalsIgnoreCase("null")) {
						vTmpHandle.add("");
					} else {
						vTmpHandle.add(sTmpHandle.trim());
					}
				}
				vHandle.add(vTmpHandle);
				vTmpHandle = null;
			}
			return vHandle;
		} catch (Exception e) {
		} finally {
			close();
		}

		return vHandle;
	}

	@SuppressWarnings({ "rawtypes", "static-access", "unchecked" })
	public List getListResultSet(String sCmd, String[] types, String[] values) {
		List<Object> vHandle = new ArrayList<Object>();
		try {
			if (sCmd.length() == 0) {
				return vHandle;
			}
			sCmd = sCmd.toLowerCase();

			conn = this.getConnection(dbtype);
			ps = conn.prepareStatement(sCmd);
			setPS(ps, types, values);
			rs = ps.executeQuery();

			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				List vTmpHandle = new ArrayList(iColumn);
				for (int i = 1; i <= iColumn; i++) {
					String sTmpHandle = rs.getString(i) + " ";
					if (sTmpHandle.trim().equalsIgnoreCase("null")) {
						vTmpHandle.add("");
					} else {
						vTmpHandle.add(sTmpHandle.trim());
					}
				}
				vHandle.add(vTmpHandle);
				vTmpHandle = null;
			}
			return vHandle;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}

		return vHandle;
	}

	public void setPS(PreparedStatement ps, String[] types, String[] values)
			throws NumberFormatException, SQLException {
		if (types == null) {
			return;
		}
		for (int i = 0; i < types.length; i++) {
			if (types[i].toLowerCase().equals("string")) {
				ps.setString(i + 1, values[i]);
				continue;
			}
			if (types[i].toLowerCase().equals("int")) {
				ps.setInt(i + 1, Integer.parseInt(values[i]));
				continue;
			}
			if (types[i].toLowerCase().equals("double")) {
				ps.setDouble(i + 1, Double.parseDouble(values[i]));
				continue;
			}
		}
	}

	/*
	 * public String filterSQL(String sql) { return sql.replaceAll("%20@",
	 * "").replaceAll("exec", "").replaceAll( "declare",
	 * "").replaceAll("<script", "").replaceAll("<if", "")
	 * .replaceAll("truncate", "").replaceAll("drop", "").replaceAll( "insert",
	 * "").replaceAll("update", "").replaceAll( "delete",
	 * "").replaceAll("sysfiles", "").replaceAll( "sysindexes",
	 * "").replaceAll("sysobjects", "") .replaceAll("syscolumns",
	 * "").replaceAll("syscomments", ""); }
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException se) {
		}
	}
}
